package db.tools;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Creates a new database to hold Bubble Chart data in
 * @author alexey
 *
 */
public class CreateDatabase {

	private Connection conn;
	
	CreateDatabase(Connection conn) {
		this.conn = conn;
	}
	
	void create() throws SQLException {
	     Statement create = conn.createStatement();
	     create.execute("CREATE TABLE IF NOT EXISTS knot (id INT AUTO_INCREMENT PRIMARY KEY, t TIMESTAMP)");
	     create.execute("CREATE TABLE IF NOT EXISTS nominal (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR)");
	     create.execute("CREATE TABLE IF NOT EXISTS nominal_value (id INT AUTO_INCREMENT PRIMARY KEY, nominal_id INT, value VARCHAR)");
	     create.execute("CREATE TABLE IF NOT EXISTS numerical (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR)");
	     create.execute("CREATE TABLE IF NOT EXISTS knot_nominal (id INT AUTO_INCREMENT PRIMARY KEY, knot_id INT, nominal_value_id INT)");
	     create.execute("CREATE TABLE IF NOT EXISTS knot_numerical (id INT AUTO_INCREMENT PRIMARY KEY, knot_id INT, numerical_id INT, value DECIMAL)");

	     create.execute("ALTER TABLE knot_nominal ADD CONSTRAINT IF NOT EXISTS knot_nominal_constraint "+
	    		"FOREIGN KEY (knot_id) REFERENCES knot (id) ON DELETE CASCADE"); 
	     create.execute("ALTER TABLE knot_numerical ADD CONSTRAINT IF NOT EXISTS knot_numerical_constraint "+
		 		"FOREIGN KEY (knot_id) REFERENCES knot (id) ON DELETE CASCADE"); 
	     
	     create.execute("CREATE INDEX IF NOT EXISTS knot_t ON knot (t)");


	     create.close();		
	}
	
	/**
	 * @param args
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		 Class.forName("org.h2.Driver");
	     Connection conn = DriverManager.getConnection("jdbc:h2:resources/bubble_data", "sa", "");
	     CreateDatabase c = new CreateDatabase(conn);
	     c.create();
	     conn.close();
	}

}
